﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using MySql.Data.MySqlClient;
using Models;
using Common;

namespace DAL
{
    /// <summary>
    /// 订单业务的增删改查
    /// </summary>
   public  class OrderDAL
    {
        //确认订单生产
        public bool EnsureManufactureOrder(string danhao)
        {
            int result = 0;
            result = SqlHelper.ExecuteNonQuery("update mainOrder set isManufacture=1 where danhao =@danhao and isdel=0", new MySqlParameter("@danhao", danhao));
            if (result == 1) return true;
            else return false;
        }

        //取消订单生产
        public bool CancelManufactureOrder(string danhao)
        {
            int result = 0;
            result = SqlHelper.ExecuteNonQuery("update mainOrder set isManufacture=0 where danhao =@danhao and isdel=0", new MySqlParameter("@danhao", danhao));
            if (result == 1) return true;
            else return false;
        }

       //获取是否下生产生产
        public object QueryMainOrderByDanhaoIsManufacture(string danhao)
        {
            return SqlHelper.ExecuteScalar("select isManufacture from mainorder where  isdel=0 and danhao=@danhao", new MySqlParameter("@danhao", danhao));
        }
       //获取门型计算公式
       public DataTable GetExpression(string mx) {
           return SqlHelper.ExecuteDataTable("select * from mxexpression where mxtype=@mxtype and isdel=0", new MySqlParameter("@mxtype", mx)); ;
       }

       //测试
       public DataTable GetMainOrder() {
           DataTable dt = new DataTable();
           return dt;
       }
       //详细列表
       public DataTable GetOrderDetail() {
           DataTable dt = SqlHelper.ExecuteDataTable("select * from detailorder where 1 =@abc",
               new MySqlParameter("@abc",1));
           return dt;
       }
       //根据id获取主单信息
       public MainOrder GetMainOrderById(string mid)
       {
           DataTable dt = SqlHelper.ExecuteDataTable("select * from mainorder where mid = @mid and isdel=0",
               new MySqlParameter("@mid", mid));
           if (dt.Rows.Count == 1) {
               return ToMainOrder(dt.Rows[0]);
           }
           else if (dt.Rows.Count <= 0)
           {
               return null;
           }
           else {
               throw new Exception("存在重复值");
           }
       }
       //转换成mainorder 模型
       public MainOrder ToMainOrder(DataRow dr) {
           MainOrder mo = new MainOrder();
           mo.Mid = Convert.ToInt32(dr["mid"]);
           mo.Danhao = dr["danhao"].ToString();
           mo.Address = dr["address"].ToString();
           mo.Phone = dr["phone"].ToString();
           mo.Username = dr["username"].ToString();
           mo.AddTime = dr["addtime"].ToString();
           mo.AddUser = dr["adduser"].ToString();
           mo.EditTime = dr["editTime"].ToString();
           mo.EditUser = dr["editUser"].ToString();
           mo.FinishDate = dr["finishdate"].ToString();
           mo.Bzbz = dr["bzbz"].ToString();
           mo.Bzfy = dr["bzfy"].ToString();
           mo.Isdel = Convert.ToInt32(dr["isdel"]);
           mo.PlanOrder = dr["planorder"].ToString();
           mo.Sname = dr["sname"].ToString();
           mo.DealerId = dr["dealerid"].ToString();
           mo.DealerName = dr["dealername"].ToString();
           mo.HanderId = dr["handerid"].ToString();
           mo.HanderName = dr["handername"].ToString();
           mo.HanderPrice = dr["handerprice"].ToString();
           mo.HanderNum = dr["handernum"].ToString();
           mo.HanderToal = dr["handertotal"].ToString();
           mo.Hingeid = dr["Hingeid"].ToString();
           mo.Hingename = dr["hingename"].ToString();
           mo.HingeDesc = dr["hingeDesc"].ToString();
           mo.Hingeprice = dr["hingeprice"].ToString();
           mo.HingeTotal = dr["hingeTotal"].ToString();
           mo.HingeNum = dr["HingeNum"].ToString();
           mo.ContactName = dr["ContactName"].ToString();
           return mo;
       }
       //保存数据
       public int SaveMainOrderData(MainOrder mo) {
           return SqlHelper.ExecuteNonQuery("insert into mainorder(danhao,address,sname,phone,planorder,username,addtime,edittime,adduser,edituser,bzfy,bzbz,isdel,dealername,dealerid,handerid,handername,handerprice,handertotal,handernum,hingeid,hingename,hingeprice,hingetotal,hingenum,contactname,hingeDesc)" +
               "values(@danhao,@address,@sname,@phone,@planorder,@username,@addtime,@edittime,@adduser,@edituser,@bzfy,@bzbz,@isdel,@dealername,@dealerid,@handerid,@handername,@handerprice,@handertotal,@handernum,@hingeid,@hingename,@hingeprice,@hingetotal,@hingenum,@contactname,@hingeDesc)",
               new MySqlParameter("@danhao", mo.Danhao), new MySqlParameter("@address", mo.Address), new MySqlParameter("@sname", mo.Sname),
               new MySqlParameter("@phone", mo.Phone), new MySqlParameter("@planorder", mo.PlanOrder), new MySqlParameter("@username", RemeberName.Username),
               new MySqlParameter("@addtime", DateTime.Now.ToString()), new MySqlParameter("@edittime",  DateTime.Now.ToString()), new MySqlParameter("@adduser", RemeberName.Username),
               new MySqlParameter("@edituser", RemeberName.Username), new MySqlParameter("@bzfy", mo.Bzfy), new MySqlParameter("@bzbz", mo.Bzbz),
               new MySqlParameter("@isdel", "0"), new MySqlParameter("@dealername", mo.DealerName), new MySqlParameter("@dealerid", mo.DealerId), new MySqlParameter("@handerid", mo.HanderId), new MySqlParameter("@handername", mo.HanderName), 
               new MySqlParameter("@handerprice", mo.HanderPrice), new MySqlParameter("@handertotal", mo.HanderToal),new MySqlParameter("@handernum", mo.HanderNum),
               new MySqlParameter("@hingeid", mo.Hingeid), new MySqlParameter("@hingename", mo.Hingename), new MySqlParameter("@hingeprice", mo.Hingeprice), new MySqlParameter("@hingetotal", mo.HingeTotal), new MySqlParameter("@hingenum", mo.HingeNum), new MySqlParameter("@contactname", mo.ContactName),new MySqlParameter("@hingeDesc",mo.HingeDesc));
       }
       //查询主单所有分页
       public DataTable ListMainOrderAll(Dictionary<string, string> dic,int pageCurrent,int pageSize)
       {
           string sql = string.Empty;
           if (!string.IsNullOrWhiteSpace(dic["ordernum"]))
           {
               sql += " and danhao like '%" + dic["ordernum"] + "%'";
           }
           if (!string.IsNullOrWhiteSpace(dic["contract"]))
           {
               sql += " and planOrder like '%" + dic["contract"] + "%'";
           }
           if (!string.IsNullOrWhiteSpace(dic["phone"]))
           {
               sql += " and phone like '%" + dic["phone"] + "%'";
           }
           if (!string.IsNullOrWhiteSpace(dic["sname"]))
           {
               sql += " and sname like '%" + dic["sname"] + "%'";
           }
           if (!string.IsNullOrWhiteSpace(dic["isManufacture"]) && !"-1".Equals(dic["isManufacture"]))
           {
               sql += " and isManufacture =" + dic["isManufacture"];
           }
           return SqlHelper.ExecuteDataTable("select * from mainOrder where isdel=@isdel " + sql + "  order by UNIX_TIMESTAMP(addtime) DESC limit  " + (pageCurrent - 1) * pageSize + "," + pageSize,
               new MySqlParameter("@isdel", "0"));
       }

       //查询主单所有
       public DataTable ListMainOrderAll(Dictionary<string,string> dic) {
           string sql = string.Empty;
           if (!string.IsNullOrWhiteSpace(dic["ordernum"])) {
               sql += " and danhao like '%" + dic["ordernum"] + "%'";
           }
           if (!string.IsNullOrWhiteSpace(dic["contract"])) {
               sql += " and planOrder like '%" + dic["contract"] + "%'";
           }
           if (!string.IsNullOrWhiteSpace(dic["phone"]))
           {
               sql += " and phone like '%" + dic["phone"] + "%'";
           }
           if (!string.IsNullOrWhiteSpace(dic["sname"]))
           {
               sql += " and sname like '%" + dic["sname"] + "%'";
           }
           return SqlHelper.ExecuteDataTable("select * from mainOrder where isdel=@isdel " + sql + "  order by UNIX_TIMESTAMP(edittime) DESC",
               new MySqlParameter("@isdel","0"));
       }
       //根据单号查询详细订单
       public DataTable GetDetailOrderByDanhao(string danhao) {
           return SqlHelper.ExecuteDataTable("select * from detailorder where danhao= @danhao and isdel=0  order by xh asc",
               new MySqlParameter("@danhao", danhao));
       }

       //更新主订单数据
       public int updateMainOrderDat(MainOrder mo) {
           int result = -1;
           result = SqlHelper.ExecuteNonQuery("update mainorder set danhao=@danhao,address=@address,sname=@sname,phone=@phone,planOrder=@planOrder,username=@username,"+
               "bzfy=@bzfy,bzbz=@bzbz ,editTime=@editTime,editUser=@editUser ,dealerid=@dealerid, dealername=@dealername,handerid=@handerid,handername=@handername,handerprice=@handerprice,handertotal=@handertotal,handernum=@handernum,hingeid=@hingeid,hingename=@hingename,hingenum=@hingenum,hingeprice=@hingeprice,hingetotal=@hingetotal,contactname=@contactname,hingeDesc=@hingeDesc where mid=@mid",
               new MySqlParameter("@danhao", mo.Danhao), new MySqlParameter("@address", mo.Address), new MySqlParameter("@sname", mo.Sname),
               new MySqlParameter("@phone", mo.Phone), new MySqlParameter("@planOrder", mo.PlanOrder), new MySqlParameter("@username", RemeberName.Username),
                new MySqlParameter("@bzfy", mo.Bzfy), new MySqlParameter("@bzbz", mo.Bzbz), new MySqlParameter("@editTime", DateTime.Now.ToString()),
               new MySqlParameter("@editUser", RemeberName.Username), new MySqlParameter("@dealerid", mo.DealerId), new MySqlParameter("@dealername", mo.DealerName), new MySqlParameter("@handerid", mo.HanderId), new MySqlParameter("@handername", mo.HanderName), new MySqlParameter("@handerprice", mo.HanderPrice), new MySqlParameter("@handertotal", mo.HanderToal), new MySqlParameter("@handernum", mo.HanderNum),
               new MySqlParameter("@hingeid", mo.Hingeid), new MySqlParameter("@hingename", mo.Hingename), new MySqlParameter("@hingenum", mo.HingeNum), new MySqlParameter("@hingeprice", mo.Hingeprice), new MySqlParameter("@hingetotal", mo.HingeTotal), new MySqlParameter("@contactname", mo.ContactName), new MySqlParameter("@hingeDesc",mo.HingeDesc), new MySqlParameter("@mid", mo.Mid));
           //DataTable dt = SqlHelper.ExecuteDataTable("");
           return result;
       }

       //确认订单
       public bool EnsureOrder(string danhao) {
           int result = 0;
           result += SqlHelper.ExecuteNonQuery("update mainOrder set isover=1 where danhao =@danhao and isdel=0", new MySqlParameter("@danhao", danhao));
           result += SqlHelper.ExecuteNonQuery("update detailOrder set isover=1 where danhao=@danhao and isdel=0", new MySqlParameter("@danhao", danhao));
           if (result >1) return true;
           else return false;
       }
       //取消订单
       public bool CancelOrder(string danhao)
       {
           int result = 0;
           result += SqlHelper.ExecuteNonQuery("update mainOrder set isover=0 where danhao =@danhao and isdel=0", new MySqlParameter("@danhao", danhao));
           result += SqlHelper.ExecuteNonQuery("update detailOrder set isover=0 where danhao=@danhao and isdel=0", new MySqlParameter("@danhao", danhao));
           result += SqlHelper.ExecuteNonQuery("delete from detailorderinfo where danhao=@danhao",new MySqlParameter("@danhao",danhao));
           if (result > 1) return true;
           else return false;
       }
     
       //保存订单详情
       public int SaveDetailOrder(DetailOrder dlo) {
           int result = 0;
           result = SqlHelper.ExecuteNonQuery("INSERT INTO detailorder ( danhao, zslb, mxbh,mxbhsm, kxbh, xbmc, pjfs, hou, gao, kuan, xbxs, num, mianji, price, total, direction, color, colorid, xbwidth, xbheight,xbtop ,xbTopHeight, xbDownHeight, finsh, addTime, editTime, addUser, editUser, isdel, isover, isglass, glassId, glassType, glassprice, iszh, isold, oldprice, otherprice, otherSm, bzbz, wlsl,oldcolor,oldcolorid,iszt,ztcd,xbyl,isspecial,specialname,specialprice,specialtotal,glass,issoft,wenlu,packageId,packagename,packageprice,packagetotal,isother,decid,decname,xbcolor,parentname) VALUES" +
                     "(@danhao,@zslb,@mxbh,@mxbhsm,@kxbh,@xbmc,@pjfs,@hou,@gao,@kuan,@xbxs,@num,@mianji,@price,@total,@direction,@color,@colorid,@xbwidth,@xbheight,@xbtop,@xbTopHeight,@xbDownHeight,@finsh,@addTime,@editTime,@addUser,@editUser,@isdel,@isover,@isglass,@glassId,@glassType,@glassprice,@iszh,@isold,@oldprice,@otherprice,@otherSm,@bzbz,@wlsl,@oldcolor,@oldcolorid,@iszt,@ztcd,@xbyl,@isspecial,@specialname,@specialprice,@specialtotal,@glass,@issoft,@wenlu,@packageid,@packagename,@packageprice,@packagetotal,@isother,@decid,@decname,@xbcolor,@parentname)",
                     new MySqlParameter("@danhao", dlo.Danhao), new MySqlParameter("@zslb", dlo.Zslb), new MySqlParameter("@mxbh", dlo.Mxbh), new MySqlParameter("@mxbhsm", dlo.MxbhSm), new MySqlParameter("@kxbh", dlo.Kxbh), new MySqlParameter("@xbmc", dlo.Xbmc), new MySqlParameter("@pjfs", dlo.Pjfs), new MySqlParameter("@hou", dlo.Hou), new MySqlParameter("@gao", dlo.Gao), new MySqlParameter("@kuan", dlo.Kuan), new MySqlParameter("@xbxs", dlo.Xbxs),
                     new MySqlParameter("@num", dlo.Num), new MySqlParameter("@mianji", dlo.Mianji), new MySqlParameter("@price", dlo.Price), new MySqlParameter("@total", dlo.Total), new MySqlParameter("@direction", dlo.Direction), new MySqlParameter("@color", dlo.Color), new MySqlParameter("@colorid", dlo.ColorId), new MySqlParameter("@xbwidth", dlo.Xbwidth), new MySqlParameter("@xbheight", dlo.Xbheight), new MySqlParameter("@xbtop", dlo.XbTop), new MySqlParameter("@xbTopHeight", dlo.XbTopHeight),
                     new MySqlParameter("@xbDownHeight", dlo.xbDownHeight), new MySqlParameter("@finsh", "0"), new MySqlParameter("@addTime", DateTime.Now.ToString()), new MySqlParameter("@editTime", DateTime.Now.ToString()), new MySqlParameter("@addUser", RemeberName.Username), new MySqlParameter("@editUser", RemeberName.Username), new MySqlParameter("@isdel", "0"), new MySqlParameter("@isover", "0"), new MySqlParameter("@isglass", dlo.IsGlass), new MySqlParameter("@glassId", dlo.GlassId),
                     new MySqlParameter("@glassType", dlo.GlassType), new MySqlParameter("@glassprice", dlo.GlassPrice), new MySqlParameter("@iszh", dlo.IsZh), new MySqlParameter("@isold", dlo.IsOld), new MySqlParameter("@oldprice", dlo.OldPrice), new MySqlParameter("@otherprice", dlo.OtherPrice), new MySqlParameter("@otherSm", dlo.OtherSm), new MySqlParameter("@bzbz", dlo.Bzbz), new MySqlParameter("@wlsl", dlo.Wlsl), new MySqlParameter("@oldcolor", dlo.OldColor), new MySqlParameter("@oldcolorid", dlo.OldColorId),
                     new MySqlParameter("@iszt", dlo.IsZt), new MySqlParameter("@ztcd", dlo.Ztcd), new MySqlParameter("@xbyl", dlo.xbyl), new MySqlParameter("@isspecial", dlo.IsSpecial), new MySqlParameter("@specialname", dlo.SpecialName), new MySqlParameter("@specialprice", dlo.SpecialPrice), new MySqlParameter("@specialtotal", dlo.SpecialTotal),new MySqlParameter("@glass",dlo.Glass),new MySqlParameter("@issoft",dlo.IsSoft),new MySqlParameter("@wenlu",dlo.Wenlu),
                     new MySqlParameter("@packageid", dlo.PackageId), new MySqlParameter("@packagename", dlo.PackageName), new MySqlParameter("@packageprice", dlo.PackagePrice), new MySqlParameter("@packagetotal", dlo.PackageTotal), new MySqlParameter("@isother", dlo.IsOther), new MySqlParameter("@decid", dlo.DecId), new MySqlParameter("@decname", dlo.DecName), new MySqlParameter("@xbcolor", dlo.XbColor),new MySqlParameter("@parentname",dlo.ParentName));
           return result;
       }

       //保存订单详情且返回主键
       public long SaveDetailOrderId(DetailOrder dlo)
       {
           long result = 0;
           result = SqlHelper.ExecuteNonQueryId("INSERT INTO detailorder ( danhao, zslb, mxbh,mxbhsm, kxbh, xbmc, pjfs, hou, gao, kuan, xbxs, num, mianji, price, total, direction, color, colorid, xbwidth, xbheight,xbtop, xbTopHeight, xbDownHeight, finsh, addTime, editTime, addUser, editUser, isdel, isover, isglass, glassId, glassType, glassprice, iszh, isold, oldprice, otherprice, otherSm, bzbz, wlsl,oldcolor,oldcolorid,iszt,ztcd,xbyl,isspecial,specialname,specialprice,specialtotal,glass,issoft,wenlu,packageId,packagename,packageprice,packagetotal) VALUES" +
                     "(@danhao,@zslb,@mxbh,@mxbhsm,@kxbh,@xbmc,@pjfs,@hou,@gao,@kuan,@xbxs,@num,@mianji,@price,@total,@direction,@color,@colorid,@xbwidth,@xbheight,@xbtop,@xbTopHeight,@xbDownHeight,@finsh,@addTime,@editTime,@addUser,@editUser,@isdel,@isover,@isglass,@glassId,@glassType,@glassprice,@iszh,@isold,@oldprice,@otherprice,@otherSm,@bzbz,@wlsl,@oldcolor,@oldcolorid,@iszt,@ztcd,@xbyl,@isspecial,@specialname,@specialprice,@specialtotal,@glass,@issoft,@wenlu,@packageid,@packagename,@packageprice,@packagetotal)",
                     new MySqlParameter("@danhao", dlo.Danhao), new MySqlParameter("@zslb", dlo.Zslb), new MySqlParameter("@mxbh", dlo.Mxbh), new MySqlParameter("@mxbhsm", dlo.MxbhSm), new MySqlParameter("@kxbh", dlo.Kxbh), new MySqlParameter("@xbmc", dlo.Xbmc), new MySqlParameter("@pjfs", dlo.Pjfs), new MySqlParameter("@hou", dlo.Hou), new MySqlParameter("@gao", dlo.Gao), new MySqlParameter("@kuan", dlo.Kuan), new MySqlParameter("@xbxs", dlo.Xbxs),
                     new MySqlParameter("@num", dlo.Num), new MySqlParameter("@mianji", dlo.Mianji), new MySqlParameter("@price", dlo.Price), new MySqlParameter("@total", dlo.Total), new MySqlParameter("@direction", dlo.Direction), new MySqlParameter("@color", dlo.Color), new MySqlParameter("@colorid", dlo.ColorId), new MySqlParameter("@xbwidth", dlo.Xbwidth), new MySqlParameter("@xbheight", dlo.Xbheight), new MySqlParameter("@xbtop", dlo.XbTop), new MySqlParameter("@xbTopHeight", dlo.XbTopHeight),
                     new MySqlParameter("@xbDownHeight", dlo.xbDownHeight), new MySqlParameter("@finsh", "0"), new MySqlParameter("@addTime", DateTime.Now.ToString()), new MySqlParameter("@editTime", DateTime.Now.ToString()), new MySqlParameter("@addUser", RemeberName.Username), new MySqlParameter("@editUser", RemeberName.Username), new MySqlParameter("@isdel", "0"), new MySqlParameter("@isover", "0"), new MySqlParameter("@isglass", dlo.IsGlass), new MySqlParameter("@glassId", dlo.GlassId),
                     new MySqlParameter("@glassType", dlo.GlassType), new MySqlParameter("@glassprice", dlo.GlassPrice), new MySqlParameter("@iszh", dlo.IsZh), new MySqlParameter("@isold", dlo.IsOld), new MySqlParameter("@oldprice", dlo.OldPrice), new MySqlParameter("@otherprice", dlo.OtherPrice), new MySqlParameter("@otherSm", dlo.OtherSm), new MySqlParameter("@bzbz", dlo.Bzbz), new MySqlParameter("@wlsl", dlo.Wlsl), new MySqlParameter("@oldcolor", dlo.OldColor), new MySqlParameter("@oldcolorid", dlo.OldColorId),
                     new MySqlParameter("@iszt", dlo.IsZt), new MySqlParameter("@ztcd", dlo.Ztcd), new MySqlParameter("@xbyl", dlo.xbyl), new MySqlParameter("@isspecial", dlo.IsSpecial), new MySqlParameter("@specialname", dlo.SpecialName), new MySqlParameter("@specialprice", dlo.SpecialPrice), new MySqlParameter("@specialprice", dlo.SpecialTotal), new MySqlParameter("@glass", dlo.Glass), new MySqlParameter("@issoft", dlo.IsSoft), new MySqlParameter("@wenlu", dlo.Wenlu),
                     new MySqlParameter("@packageid", dlo.PackageId), new MySqlParameter("@packagename", dlo.PackageName), new MySqlParameter("@packageprice", dlo.PackagePrice), new MySqlParameter("@packagetotal", dlo.PackageTotal));
           return result;
       }

       //更新订单详情
       public int UpdateDetailOrder(DetailOrder dlo) {
           int result = 0;
           result = SqlHelper.ExecuteNonQuery("update detailorder set danhao=@danhao, zslb=@zslb, mxbh=@mxbh, mxbhsm=@mxbhsm,kxbh=@kxbh, xbmc=@xbmc, pjfs=@pjfs, hou=@hou, gao=@gao, kuan=@kuan, xbxs=@xbxs, num=@num, mianji=@mianji, price=@price, " +
           "total=@total, direction=@direction, color=@color, colorid=@colorid, xbwidth=@xbwidth, xbheight=@xbheight,xbtop=@xbtop, xbTopHeight=@xbTopHeight, xbDownHeight=@xbDownHeight,   editTime=@editTime,  editUser=@editUser,"+
           "isglass=@isglass, glassId=@glassId, glassType=@glassType, glassprice=@glassprice, iszh=@iszh, isold=@isold,oldcolor=@oldcolor,oldcolorid=@oldcolorid ,oldprice=@oldprice, otherprice=@otherprice, otherSm=@otherSm, bzbz=@bzbz, wlsl=@wlsl, "+
           "iszt=@iszt ,ztcd=@ztcd,xbyl=@xbyl,isspecial=@isspecial ,specialname=@specialname,specialprice=@specialprice,specialtotal=@specialtotal,glass=@glass,issoft=@issoft,wenlu=@wenlu,packageid=@packageid,packagename=@packagename,packageprice=@packageprice,packagetotal=@packagetotal,isother=@isother,decid=@decid,decname=@decname,xbcolor=@xbcolor,parentname=@parentname  where did=@did",
           new MySqlParameter("@danhao", dlo.Danhao), new MySqlParameter("@zslb", dlo.Zslb), new MySqlParameter("@mxbh", dlo.Mxbh), new MySqlParameter("@mxbhsm", dlo.MxbhSm), new MySqlParameter("@kxbh", dlo.Kxbh), new MySqlParameter("@xbmc", dlo.Xbmc), new MySqlParameter("@pjfs", dlo.Pjfs), new MySqlParameter("@hou", dlo.Hou), new MySqlParameter("@gao", dlo.Gao), new MySqlParameter("@kuan", dlo.Kuan), new MySqlParameter("@xbxs", dlo.Xbxs),
                     new MySqlParameter("@num", dlo.Num), new MySqlParameter("@mianji", dlo.Mianji), new MySqlParameter("@price", dlo.Price), new MySqlParameter("@total", dlo.Total), new MySqlParameter("@direction", dlo.Direction), new MySqlParameter("@color", dlo.Color), new MySqlParameter("@colorid", dlo.ColorId), new MySqlParameter("@xbwidth", dlo.Xbwidth), new MySqlParameter("@xbheight", dlo.Xbheight), new MySqlParameter("@xbtop", dlo.XbTop), new MySqlParameter("@xbTopHeight", dlo.XbTopHeight),
                     new MySqlParameter("@xbDownHeight", dlo.xbDownHeight),  new MySqlParameter("@editTime", DateTime.Now.ToString()), new MySqlParameter("@editUser", RemeberName.Username), new MySqlParameter("@isglass", dlo.IsGlass), new MySqlParameter("@glassId", dlo.GlassId),
                     new MySqlParameter("@glassType", dlo.GlassType), new MySqlParameter("@glassprice", dlo.GlassPrice), new MySqlParameter("@iszh", dlo.IsZh), new MySqlParameter("@isold", dlo.IsOld), new MySqlParameter("@oldcolor", dlo.OldColor), new MySqlParameter("@oldcolorid", dlo.OldColorId), new MySqlParameter("@oldprice", dlo.OldPrice), new MySqlParameter("@otherprice", dlo.OtherPrice), new MySqlParameter("@otherSm", dlo.OtherSm), new MySqlParameter("@bzbz", dlo.Bzbz), new MySqlParameter("@wlsl", dlo.Wlsl),
                     new MySqlParameter("@iszt", dlo.IsZt), new MySqlParameter("@ztcd", dlo.Ztcd), new MySqlParameter("@xbyl", dlo.xbyl), new MySqlParameter("@isspecial", dlo.IsSpecial), new MySqlParameter("@specialname", dlo.SpecialName), new MySqlParameter("@specialprice", dlo.SpecialPrice), new MySqlParameter("@specialtotal", dlo.SpecialTotal), new MySqlParameter("@glass", dlo.Glass), new MySqlParameter("@issoft", dlo.IsSoft), new MySqlParameter("@wenlu", dlo.Wenlu),
                     new MySqlParameter("@packageid", dlo.PackageId), new MySqlParameter("@packagename", dlo.PackageName), new MySqlParameter("@packageprice", dlo.PackagePrice), new MySqlParameter("@packagetotal", dlo.PackageTotal), new MySqlParameter("@isother", dlo.IsOther), new MySqlParameter("@decid", dlo.DecId), new MySqlParameter("@decname", dlo.DecName), new MySqlParameter("@xbcolor", dlo.XbColor), new MySqlParameter("@parentname", dlo.ParentName), new MySqlParameter("@did", dlo.Did));
           return result;
       }

       //根据id查询订单详情
       public DetailOrder GetDetailById(string did) {
           DataTable dt = SqlHelper.ExecuteDataTable("select * from detailorder where isdel=0 and did=@did", new MySqlParameter("@did", did));
           if(dt.Rows.Count==1){
               return ToDetailOrder(dt.Rows[0]);
           }else if(dt.Rows.Count<=0){
               return null;
           }else{
            throw new Exception("存在重复值");
           }
       }

       //转换成详细订单模型
       public DetailOrder ToDetailOrder(DataRow dr) {
            DetailOrder dlo = new DetailOrder();
            dlo.Did = Convert.ToInt32(dr["did"]);
            dlo.Danhao = dr["danhao"].ToString();
            dlo.Mxbh = dr["Mxbh"].ToString();
            dlo.MxbhSm = dr["MxbhSm"].ToString();
            dlo.Kxbh = dr["Kxbh"].ToString();
            dlo.Xbmc = dr["Xbmc"].ToString();
            dlo.Pjfs = dr["Pjfs"].ToString();
            dlo.Gao = dr["Gao"].ToString();
            dlo.Hou = dr["Hou"].ToString();
            dlo.Kuan = dr["Kuan"].ToString();
            dlo.Xbxs = dr["Xbxs"].ToString();
            dlo.Num = dr["Num"].ToString();
            dlo.Mianji = dr["Mianji"].ToString();
            dlo.Price = dr["Price"].ToString();
            dlo.Total = dr["Total"].ToString();
            dlo.Direction = dr["Direction"].ToString();
            dlo.ColorId = dr["ColorId"].ToString();
            dlo.Color = dr["Color"].ToString();
            dlo.Xbwidth = dr["Xbwidth"].ToString();
            dlo.Xbheight = dr["Xbheight"].ToString();
            dlo.XbTopHeight = dr["XbTopHeight"].ToString();
            dlo.xbDownHeight = dr["xbDownHeight"].ToString();
            dlo.Finsh = Convert.ToInt32(dr["Finsh"]);
            dlo.AddTime = dr["AddTime"].ToString();
            dlo.EditTime = dr["EditTime"].ToString();
            dlo.AddUser = dr["AddUser"].ToString();
            dlo.EditUser = dr["EditUser"].ToString();
            dlo.Isdel = Convert.ToInt32(dr["Isdel"]);
            dlo.Isover = Convert.ToInt32(dr["Isover"]);
            dlo.IsGlass = Convert.ToInt32(dr["IsGlass"]);
            dlo.GlassPrice = dr["GlassPrice"].ToString();
            dlo.IsZh = Convert.ToInt32(dr["IsZh"]);
            dlo.IsOld = Convert.ToInt32(dr["IsOld"]);
            dlo.OldPrice = dr["OldPrice"].ToString();
            dlo.Zslb = Convert.ToInt32(dr["Zslb"]);
            dlo.GlassId = dr["GlassId"].ToString();
            dlo.GlassType = dr["GlassType"].ToString();
            dlo.OtherPrice = dr["OtherPrice"].ToString();
            dlo.OtherSm = dr["OtherSm"].ToString();
            dlo.Bzbz = dr["Bzbz"].ToString();
            dlo.Wlsl = dr["Wlsl"].ToString();
            dlo.OldColor = dr["OldColor"].ToString();
            dlo.OldColorId = dr["OldColorId"].ToString();
            dlo.xbyl = dr["xbyl"].ToString();
            dlo.IsSpecial = Convert.ToInt32(dr["IsSpecial"]);
            dlo.SpecialName = dr["SpecialName"].ToString();
            dlo.SpecialPrice = string.IsNullOrWhiteSpace(dr["SpecialPrice"].ToString()) ? 0 : Convert.ToDecimal(dr["SpecialPrice"]);
            dlo.SpecialTotal = string.IsNullOrWhiteSpace(dr["SpecialTotal"].ToString()) ? 0 : Convert.ToDecimal(dr["SpecialTotal"]);
            dlo.IsZt = Convert.ToInt32(dr["IsZt"]);
            dlo.Ztcd = dr["Ztcd"].ToString();
            dlo.Glass = Convert.ToInt32(dr["glass"]);
            dlo.Wenlu = dr["wenlu"].ToString();
           //新增
            dlo.XbTop = dr["xbtop"].ToString();
            dlo.PackageId = dr["PackageId"].ToString();
            dlo.PackageName = dr["PackageName"].ToString();
            dlo.PackagePrice = dr["PackagePrice"].ToString();
            dlo.PackageTotal = dr["PackageTotal"].ToString();
           //新增另加装饰
            dlo.IsOther = Convert.ToInt32(dr["IsOther"]);
            dlo.DecId = dr["DecId"].ToString();
            dlo.DecName = dr["DecName"].ToString();
            dlo.XbColor = dr["XbColor"].ToString();
           return dlo;
       }

       //根据主订单删除
       public int DelMainOrderByDanhao(string danhao ) {
           int  flag = 0;
           flag += SqlHelper.ExecuteNonQuery("update mainorder set isdel=1 where danhao=@danhao",new MySqlParameter("@danhao",danhao));
           flag += SqlHelper.ExecuteNonQuery("update detailorder set isdel=1 where danhao=@danhao",new MySqlParameter("@danhao",danhao));

           return flag;
       }

       //根据id删除子订单
       public int DelDetailOrderById(string did) {
           return SqlHelper.ExecuteNonQuery("update detailorder set isdel = 1 where did =@did",new MySqlParameter("@did",did));
       }
       //报表需要主单号
       public DataTable ReportMainOrderByDanhao(string danhao) {
           return SqlHelper.ExecuteDataTable("select * from mainorder where danhao =@danhao and isdel=0", new MySqlParameter("@danhao", danhao));
       }

       ////报表需要主单号
       public DataTable ReportMainOrderByDanhaoExcel(string danhao)
       {
           return SqlHelper.ExecuteDataTable(@"select mid ,danhao,concat_ws('  ',phone,contactname) as phone, contactname,concat_ws('  ',address,sname) as  address,dealername as sname,planorder,username,addtime,edittime,adduser,edituser,isdel,bzfy,bzbz,isover,importFinsh,
dealername,dealerid,handerprice,handerid,handername,handertotal,handernum,hingeid,hingename,hingeprice,hingetotal,hingenum,hingedesc from mainorder  where danhao =@danhao and isdel=0", new MySqlParameter("@danhao", danhao));
       }

       //报表需要订单详情物料
       public DataTable ReportWLDetailOrderByDanhao(string danhao)
       {
           return SqlHelper.ExecuteReportDataTable("select (@i:=@i+1) as xh,D.pjfs,D.mxbh,D.kxbh,D.xbmc,D.gao,D.kuan,D.hou,D.xbxs,D.num,CONCAT_ws('','边框:',D.color,';',case  when parentname is null then '' else '\r\n'  end,D.parentname,D.decname)  as color,D.mianji,D.price,D.total,D.direction,D.bzbz,D.zslb,D.isold,D.oldColor," +
           "D.otherprice,D.othersm,D.wlsl,D.oldprice,D.isglass,D.glasstype,D.glassprice,D.iszh,D.xbwidth,D.xbheight,D.xbtopheight,D.xbdownheight,D.oldColorId,D.glassId,D.isdel,D.iszt,D.ztcd,D.isspecial,D.specialname,D.specialprice,D.specialtotal, " +
            " D.packageid,D.packagename,D.packageprice,D.packagetotal from detailorder as D,(select @i:=0) as B where D.isdel=0 and D.danhao = '" + danhao + "'");
       }

       //报表需要订单详情
       public DataTable ReportDetailOrderByDanhao(string danhao) {
           return SqlHelper.ExecuteReportDataTable("select (@i:=@i+1) as xh,D.pjfs,D.mxbh,D.kxbh,D.xbmc,D.gao,D.kuan,D.hou,D.xbxs,D.num,D.color,D.mianji,D.price,D.total,D.direction,D.bzbz,D.zslb,D.isold,D.oldColor," +
           "D.otherprice,D.othersm,D.wlsl,D.oldprice,D.isglass,D.glasstype,D.glassprice,D.iszh,D.xbwidth,D.xbheight,D.xbtopheight,D.xbdownheight,D.oldColorId,D.glassId,D.isdel,D.iszt,D.ztcd,D.isspecial,D.specialname,D.specialprice,D.specialtotal, "+
            " D.packageid,D.packagename,D.packageprice,D.packagetotal from detailorder as D,(select @i:=0) as B where D.isdel=0 and D.danhao = '" + danhao+"'");
       }

       //获取门型订单
       public DataTable ReportMXOrderByDanhao(string danhao)
       {
           return SqlHelper.ExecuteReportDataTable("select (@i:=@i+1) as xh,D.pjfs,D.mxbh,D.kxbh,D.xbmc,D.gao,D.kuan,D.hou,D.xbxs,D.num,CONCAT_ws('','边框:',D.color,';',case  when parentname is null then '' else '\r\n'  end,D.parentname,D.decname)  as color,D.mianji,D.price,D.total,D.direction,D.bzbz,D.zslb,D.isold,D.oldColor," +
           "D.otherprice,D.othersm,D.wlsl,D.oldprice,D.isglass,D.glasstype,D.glassprice,D.iszh,D.xbwidth,D.xbheight,D.xbtopheight,D.xbdownheight,D.oldColorId,D.glassId,D.isdel,D.iszt,D.ztcd,D.isspecial,D.specialname,D.specialprice,D.specialtotal, "+
           "D.packageid,D.packagename,D.packageprice,D.packagetotal from detailorder as D,(select @i:=0) as B where D.isdel=0 and D.zslb=0 and   D.danhao = '" + danhao + "'");
       }

       //获取装饰线条订单
       public DataTable ReportZSXTOrderByDanhao(string danhao)
       {
           return SqlHelper.ExecuteReportDataTable("select (@i:=@i+1) as xh,D.pjfs,D.mxbh,D.kxbh,D.xbmc,D.gao,D.kuan,D.hou,D.xbxs,D.num,D.color,D.mianji,D.price,D.total,D.direction,D.bzbz,D.zslb,D.isold,D.oldColor," +
           "D.otherprice,D.othersm,D.wlsl,D.oldprice,D.isglass,D.glasstype,D.glassprice,D.iszh,D.xbwidth,D.xbheight,D.xbtopheight,D.xbdownheight,D.oldColorId,D.glassId,D.isdel,D.iszt,D.ztcd,D.isspecial,D.specialname,"
           + "D.specialprice,D.specialtotal,case D.iszt when 0 then D.gao*D.num/1000 else D.gao*D.num/1000 end  as zscd,  "+
           " D.packageid,D.packagename,D.packageprice,D.packagetotal from detailorder as D,(select @i:=0) as B where D.isdel=0 and D.zslb=1 and   D.danhao = '" + danhao + "'");
       }
       //获取无价格门型订单
       public DataTable ReportMXOrderNoPriceByDanhao(string danhao)
       {
           return SqlHelper.ExecuteReportDataTable("select (@i:=@i+1) as xh,D.pjfs,D.mxbh,D.kxbh,D.xbmc,D.gao,D.kuan,D.hou,D.xbxs,D.num,CONCAT_ws('','边框:',D.color,';',case  when parentname is null then '' else '\r\n'  end,D.parentname,D.decname)  as color,D.mianji,'' as price,'' as total,D.direction,D.bzbz,D.zslb,D.isold,D.oldColor," +
           "D.otherprice,D.othersm,D.wlsl,D.oldprice,D.isglass,D.glasstype,D.glassprice,D.iszh,D.xbwidth,D.xbheight,D.xbtopheight,D.xbdownheight,D.oldColorId,D.glassId,D.isdel,D.iszt,D.ztcd,D.isspecial,D.specialname,D.specialprice,D.specialtotal, "+
           " D.packageid,D.packagename,D.packageprice,D.packagetotal from detailorder as D,(select @i:=0) as B where D.isdel=0 and D.zslb=0 and   D.danhao = '" + danhao + "'");
       }

       //获取无价格装饰线条订单
       public DataTable ReportZSXTOrderNoPriceByDanhao(string danhao)
       {
           return SqlHelper.ExecuteReportDataTable("select (@i:=@i+1) as xh,D.pjfs,D.mxbh,D.kxbh,D.xbmc,D.gao,D.kuan,D.hou,D.xbxs,D.num,D.color,D.mianji,'' as price,'' as total,D.direction,D.bzbz,D.zslb,D.isold,D.oldColor," +
           "D.otherprice,D.othersm,D.wlsl,D.oldprice,D.isglass,D.glasstype,D.glassprice,D.iszh,D.xbwidth,D.xbheight,D.xbtopheight,D.xbdownheight,D.oldColorId,D.glassId,D.isdel,D.iszt,D.ztcd,D.isspecial,D.specialname,"
           + "D.specialprice,D.specialtotal,case D.iszt when 0 then D.gao*D.num/1000 else D.gao*D.num/1000 end  as zscd, "+
           " D.packageid,D.packagename,D.packageprice,D.packagetotal  from detailorder as D,(select @i:=0) as B where D.isdel=0 and D.zslb=1 and   D.danhao = '" + danhao + "'");
       }


       //查询除特殊工艺外的总价
       public string GetReportTotal(string danhao) {
           object o = SqlHelper.ExecuteScalar("select sum(total) from detailorder where danhao=@danhao and isdel=0", new MySqlParameter("@danhao", danhao));
           if (o == DBNull.Value || o == null)
           {
               return null;
           }
           else {
               return o.ToString();
           }
       }
       //查询特殊工艺做旧的总价
       public string GetReportOldTotal(string danhao)
       {
           object o = SqlHelper.ExecuteScalar("select sum(oldprice) from detailorder where danhao=@danhao and isdel=0", new MySqlParameter("@danhao", danhao));
           if (o == DBNull.Value || o == null)
           {
               return null;
           }
           else
           {
               return o.ToString();
           }
       }
       //查询特殊工艺玻璃芯板的价格
       public string GetReportglassTotal(string danhao)
       {
           object o = SqlHelper.ExecuteScalar("select sum(glassprice) from detailorder where danhao=@danhao and isdel=0", new MySqlParameter("@danhao", danhao));
           if (o == DBNull.Value || o == null)
           {
               return null;
           }
           else
           {
               return o.ToString();
           }
       }
       //查询订单中是否存在两个及以上做旧颜色
       public DataTable GetReportColorDistint(string danhao) {
           return SqlHelper.ExecuteDataTable("select distinct oldcolorid from detailorder where isold =1 and isdel=0 and danhao=@danhao and zslb=0",
               new MySqlParameter("@danhao",danhao));
       }

       //查询订单中是否存在两个及以上玻璃芯板
       public DataTable GetReportGlassDistint(string danhao)
       {
           return SqlHelper.ExecuteDataTable("select distinct glassid from detailorder where isglass =1 and isdel=0 and danhao=@danhao and zslb=0",
               new MySqlParameter("@danhao", danhao));
       }
       //查询订单中是否存在两个及以上装饰线条做旧颜色
       public DataTable GetReportZsxtColorDistint(string danhao)
       {
           return SqlHelper.ExecuteDataTable("select distinct oldcolorid from detailorder where isold =1 and isdel=0 and danhao=@danhao and zslb=1 and iszt=0",
               new MySqlParameter("@danhao", danhao));
       }

       //查询订单中是否存在两个及以上柱头做旧颜色
       public DataTable GetReportZtzjColorDistint(string danhao)
       {
           return SqlHelper.ExecuteDataTable("select distinct oldcolorid from detailorder where isold =1 and isdel=0 and danhao=@danhao and zslb=1 and iszt=1",
               new MySqlParameter("@danhao", danhao));
       }

       public DataTable ReportWlsl(string danhao) {
           return SqlHelper.ExecuteDataTable("select (@i:=@i+1) as xh, D.mxbhsm as wlmc,'米' as kxunit ,D.wlsl as kxyl,D.xbmc,'㎡' as xbunit,D.xbyl from detailorder D,(select @i:=0) as B where D.isdel=0 and D.zslb=0 and D.danhao=@danhao", new MySqlParameter("@danhao", danhao));
       }
       //查询最大序号
       public object GetDetailXh(string danhao) {
           return SqlHelper.FromDbValue( SqlHelper.ExecuteScalar("select max(xh) from detailorder where danhao=@danhao and isdel=0",new MySqlParameter("@danhao",danhao)));
       }
       //保存条形码
       public bool SaveBarCode(List<Barcode> ls) {
           
           List<string> sql = new List<string>();
            foreach(Barcode b in ls)
            {
                string s = "insert into barcode(danhao,imagepath,color,did,bitpath) values('{0}','{1}','{2}','{3}','{4}')";
               s= string.Format(s,b.Danhao,b.ImagePath.Replace("\\","\\\\"),b.Color,b.Did,b.Bitpath);
               sql.Add(s);
           }
            return SqlHelper.ExecuteNonQueryMore(sql);
       }
       //获取条形码数据库
       public DataTable GetBarcodeList(string danhao) {
           return SqlHelper.ExecuteDataTable("select * from barcode where danhao =@danhao", new MySqlParameter("@danhao", danhao)); ;
       }
       //根据单号获取主单号
       public DataTable GetMainOrderByDanhao(string danhao) {
           return SqlHelper.ExecuteDataTable("select danhao from mainorder where isdel=0 and danhao=@danhao",new MySqlParameter("danhao",danhao));
       }
       //获取柱头装饰线条费用单价
       public object GetZtPrice(string zsbh) {
           return SqlHelper.FromDbValue(SqlHelper.ExecuteScalar("select oldprice from zsxt where isdel =0 and zslb=1 and zsbh=@zsbh", new MySqlParameter("@zsbh", zsbh)));
       }
       //获取柱头装饰线条费用单价
       public object GetZsxtOldPrice(string oldcolorid)
       {
           return SqlHelper.FromDbValue(SqlHelper.ExecuteScalar("select mprice from menxingcolor where isdel =0 and isold=1 and gylb=1 and mid=@mid", new MySqlParameter("@mid", oldcolorid)));
       }


       //查询经销商
       public DataTable GetDealer() {
           return SqlHelper.ExecuteDataTable("select * from dealerinfo where isdel=@isdel",new MySqlParameter("@isdel","0"));
       }
       //拉手
       public DataTable Gethander() {
           return SqlHelper.ExecuteDataTable("select * from hander where isdel =@isdel",new MySqlParameter("@isdel","0"));
       }

       //批量插入前先查询
       public DataTable QueryDetailOrderByDanhao(string danhao) {
           return SqlHelper.ExecuteDataTable("select did,danhao,zslb,mxbh,mxbhsm,gao,kuan,xbxs,num,direction,color,xbwidth,xbheight,xbTopHeight,xbDownHeight,addTime,editTime,addUser,editUser,isdel,isover,iszh,barcode,iszt from detailorder where isdel=0 and danhao=@danhao", new MySqlParameter("@danhao", danhao));
       } 
       //查询是否已经确认
       public object QueryMainOrderByDanhaoIsEnsure(string danhao) {
           return SqlHelper.ExecuteScalar("select isover from mainorder where  isdel=0 and danhao=@danhao",new MySqlParameter("@danhao",danhao));
       }

       //批量保存
       public int SaveBatchInfo(DataTable dt) {
           return SqlHelper.BatchInsert(dt);
       }
       //铰链
       public DataTable GetHinge() {
           return SqlHelper.ExecuteDataTable("select * from hingeinfo where isdel =@isdel", new MySqlParameter("@isdel","0"));
       }
       //根据管理员姓名 查询权限
       public object GetIsAdminByName(string username) {
           return SqlHelper.ExecuteScalar("select isadmin from user where isdel=0 and username=@username", new MySqlParameter("@username", username));
       }
       //更新详情订单单号
       public int UpdateDetailOrderNum(string danhao,MainOrder mo) {
           return SqlHelper.ExecuteNonQuery("update detailorder set danhao =@newdanhao where danhao = @ydh and isdel=0", new MySqlParameter("@newdanhao", mo.Danhao), new MySqlParameter("@ydh", danhao));
       }


       
    }
}
